A database management system (DBMS) facilitates interaction with database data. The DBMS efficiently manages requests or queries from users and programs so that these entities are free from having to know specifics pertaining to how and where data is physically stored. Furthermore, in handling requests, the DBMS ensures the integrity and security with respect to the data. Although other systems are emerging, the relational database management system (RDBMS) remains the most common DBMS.
Database management systems provide functionality that is essential to development and execution of business applications. Accordingly, DBMS are increasingly employed as a core component in applications. However, at present, the problem of tuning DBMS for achieving required performance is considerable and results in high total cost of ownership (TCO).
The performance of a database system depends crucially on its physical database design. In particular, physical design structures such as indexes, materialized views, and vertical and horizontal partitioning, among other things are selectively employed to improve performance significantly. For example, consider a particular database query issued numerous times throughout a day. If a database view is materialized to capture the query answer, response time can be reduced substantially.
Although typically a database administrator (DBA) is responsible for database tuning, reliance on an automated tool to make good design decisions has become increasingly common and necessary, especially with large-scale deployment of databases. Given a workload on a database system, these automated tools effectively reduce the DBA's burden by recommending a physical database design, which may be refined later, if necessary, and implemented by the DBA.
Automated physical design tuning solutions are currently offered by major database vendors. Conventional design advisors or tools are designed to recommend one or more of indexes, materialized query tables (i.e., materialized views), shared nothing partitions and multidimensional clustering of tables, amongst other structures. In general, conventional techniques adopt the same problem definition, namely given a workload as input recommend a single configuration or physical database design that provides the best overall performance for the entire workload.